Ok, so I've been playing around with SQL Server 2005 64-bit Dev Edition in readiness to phase out my existing SQL 2000 box. I've been having difficulty with importing fixed-width text data with SSIS. To explain, we use a linux based ERP system which is stored in a non-standard, encrypted flat-file format. So, each evening, I have a cron job spin off some shell scripts that use the ERP's report tool dump all of the relevant databases I need into a fixed-width format text file. The ERP platform also has a db layout report which is also in text format, I use this file (mangle it with VB first) and dump it back out into the format used to make a DSN file.
DSN connector file (dbname.dsn)
[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=text
Extensions=None,asc,csv,tab,txt
DriverID=27
DefaultDir=C:\ADVDB\IMPORTED\common\current\advdb009
DBQ=C:\ADVDB\IMPORTED\common\current\advdb009
Because ODBC/DSNs are nifty enough to use a schema.ini file to lookup the data types, length and field names, I used that same db layout file to generate that as well.
schema.ini:
[advdb009.txt]
Format=FixedLength
ColNameHeader=False
MaxScanRows=0
CharacterSet=OEM
Col1=AccountNumber Char Width 15
Col2=Description Char Width 41
Col3=ContraAccountNumber Char Width 15
Col4=Type Char Width 2
Col5=Status Char Width 2
Col6=PositionOnBalanceSheet Char Width 2
Col7=PostingStatus Char Width 2
Col8=JobCostingTemplate Char Width 2
Col9=DailyActivityAmount Float Width 15
*snip*
I then put this schema.ini file in the same directory as the text file containing the fixed-width data, and its ready to go. In 2000/DTS, I simply launch the import/export wizard, point my data source to "Other (ODBC Data Source)" and point to the DSN file I created. Select my destination as a new SQL table on my server. Then tell the import wizard to do a straight across data copy, no transformations (except drop the destination table)... Save it as a local DTS package and finish the wizard. Once the package is saved, I use dtsrun from a cmd line in a bunch of batch files to re-populate the updated data from my ERP system to SQL everynight at 3am. Next morning, I can run fresh reports on my sales, inventory and financials from data of the previous day using Crystal or OLAP.
Now enters SSIS... Using the SSIS import wizard it does not give me the option to use a DSN source like DTS did. I've tried the .NET Framework for ODBC but it doesn't recognize the schema files, unless I'm not doing it right. Using the flatfile import method from SSIS requires that I hand enter the data types, widths and field name. As I have over 4000 fields across 26 tables, this is not practical. I also don't want to be required to hand-code a SSIS package in visual studio to resolve this, because again, I have to hand-code the schema when I already have it in a known ODBC schema file.
People have told me to use the DTS runner built into the SQL 2005, unfortunately my attempts to use it have failed mostly because the packages were written in SQL2000/32bit and I'm running the packages on 64-bit SQL 2005. They've given me alot of compile errors in visual studio. I also won't switch to a 32-bit version of SQL2005, because thats the whole point of moving to the new version and 64-bit technology, I want the extra horsepower and memory handling.
Has anyone else had this problem? Can anyone point me in the right direction on how to handle my import issue? I've picked up a few SSIS books but all they talk about is moving data back and forth with known server types and using the SSIS designer, nothing specific to working with fixed-width files, DSN or ODBC methods. Google searches has also ended nowhere due to the newness of SQL2005/SSIS.
Any help would be appreciated.
This is what I get when I attempt to use the ".NET Framework for ODBC" as a driver using the connection string:
Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\ADVDB\IMPORTED\common\current\advdb009
defaultdir=C:\ADVDB\IMPORTED\common\current\advdb009;driverid=27;extensions=None,asc,csv,tab,txt;fil=text;
filedsn=C:\AdvDB\dsn\common_advdb009.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;
safetransactions=0;threads=3;uid=admin;usercommitsync=Yes
Tell the wizard to dump into a SQL Native Client on 2005. The data copy is grayed so I have to write a query. So, if I enter something simple like :
select accountnumber from advdb009.txt
or
select * from advdb009.txt
I get this :
===================================
The statement could not be parsed. (SQL Server Import and Export Wizard)
===================================
ERROR [HY000] [Microsoft][ODBC Text Driver] In the text file specification 'advdb009.txt', the Col1 option is invalid. (odbcjt32.dll)
Program Location:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcDataReader.get_FieldCount()
at System.Data.Odbc.OdbcDataReader.NewSchemaTable()
at System.Data.Odbc.OdbcDataReader.GetSchemaTable()
at Microsoft.SqlServer.Dts.DtsWizard.StepQuery.ParseStatement(Boolean bReportSuccess)
If Col1 isn't supported by .NET but it is with the old ODBC/DSN style, what *IS* supported?
|||Anyone have any input on this issue?|||
Use a DSN-less connection string and you'll be able to connect to an ODBC text driver from SSIS. Works in the wizard or in BIDS.
In the wizard, select the .NET Framework provider for ODBC. For the source connection string, write in your dsn-less connection string ( know it says Access, it will work):
uid=admin;Driver={Microsoft Access 12.0 Text Driver (*.txt, *.csv)};dbq=C:\data;defaultdir=C:\data;driverid=27;extensions=txt;fil=text;maxbuffersize=2048;maxscanrows=25;pagetimeout=5;safetransactions=0;threads=3;usercommitsync=Yes;
Pick your target.
Write the query that matches your schema.ini (e.g. select * from [advdb009.txt] ). Preview the data in the next tab.
If you're doing the same thing in the Business Intel Designer, create an ADO.NET connection manager using the .NET Framework provider for ODBC with the same connection string as the above. Create a dataflow, and for your source, use a DataReaderSource, setting the SqlCommand property to "select * from [advdb009.txt]".
Well that got me closer. Funny thing is, when I get passed the destination part to the mappings, I can preview my data now. It looks great. All the columns are there with the proper schema. But, when I finish the wizard. It gets to the 4th step "Setting Source Connection" with a red x and It gives me this error message:
===================================
Could not set up data flow connections.
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
(SQL Server Import and Export Wizard)
===================================
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
({760BF532-15EE-4E4E-BA6D-287C511600B2})
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.set_ConnectionString(String pbstrConnectionString)
at Microsoft.SqlServer.Dts.DtsWizard.Execute.SetupConnections(Exception& ex)
Followed by:
===================================
Could not connect source component.
Error 0xc0047062: Source - Query [1]: System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) (SQL Server Import and Export Wizard)
===================================
The ConnectionString property has not been initialized. (Microsoft.SqlServer.DTSPipelineWrap)
Program Location:
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
at Microsoft.SqlServer.Dts.DtsWizard.Execute.SetSourceConnection(Exception& ex, TransformInfo ti)
I've read through my connection string atleast 2 dozen times and removed stuff that's not really needed to get the data. And it still generates that error message. So frustrating...
|||What is your exact connection string, at this point?|||I fixed my problem... This was what I had to use for my connection string.
FILEDSN=C:\AdvDB\dsn\common_advdb009.dsn;Uid=admin;Pwd=;
The Access Text Driver you mentioned in your previous post didn't work. The system didn't recognize the driver itself (probably because I don't have it). I'm guessing that Text Driver is from a newer version of Access or something. I have Access from Office XP version. But I'm running VS2005/SQL2003. So if the driver came from Access 2003, that would explain why it wasn't recognized.
But, I tried the standard text driver I was using before and I was able to preview my data. You found goof I did with my sql syntax was the "select * from [advdb009.txt]", I was using it without the square brackets around advdb009.txt.
So now we get to why the DSNless connection wouldn't work. Here's my guess, which I didn't notice until I looked at the connection string for the 50th time. I think the SSIS wizard freaked out because the driver I was using said this:
{Microsoft Text Driver (*.txt; *.csv)}...
Which worked to allow me to preview the data but when the wizard tried to build the code, it failed saying it doesn't fall into proper format X=Y, etc. Well, you'll notice that there is a semi-colon between *.txt and *.csv. I feel that is what threw it off. Granted if I had the correct driver that you mentioned for Access 12.0, it may have worked, because the string you gave, had a comma seperating *.txt and *.csv. I even tested your string with a semi-colon to rule that out, but the driver was still not recognized. I even changed the regular text driver from semi-colon to comma, that didn't work either, said the driver could not be found. I always thought that if the string had the curly brackets around it, it was to take all string variables inside the brackets as one value. But, I guess the parser sees the semi-colon anyway and crashes. I'd consider this a bug in my opinion.
So, the answer was to use a DSN based connection string. Which is totally stupid because even in my DSN file itself, it contains the semi-colon in the driver parameter value...
[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
The parser doesn't like the semi-colon on the dsnless connection strings, but it'll handle it if it's being read in from a 3rd party file like I'm doing.
Totally appreciate your help, you got me pointed in the right direction of where the problem was.
Thanks alot! Now I finish building these bad boys and kill off my old SQL2000 box.
