I have a security system that creates log files in a dbf format. I need to report from a weekly collection for files. I am trying to do this through Integration Services 2005. My hope is to use the for each loop.
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
My problem is the only way I know how to get the data out of the dbf file is to use a .Net ODBC provider connection. In this connection I use a select statement to pull the data from the dbf file and then an OLE DB connection to move the data into a SQL table. When only processing one file everything works just fine. The statement is: select * from c:\datafiles\security\20051107.dbf.
When adding the for loop component I assign a variable user::varFileName. This variable will pick up each file in the directory. The variable name has the direct path assigned to it ie c:\datafiles\security\20051107.dbf, etc.
My problem is how do I pass the variable to the select statement Everything I have tried give me an error message stating: Connection string property has not been initialized.
Loading multiple files
Chris Kurz
I have been able to build the connection string. I don't have a clue on how to add to the expression to create a dynamic connection string.
When I evaluate the expression I have setup @user::varFileName it give me
select * from select * from. I don't even know where it is getting this assigned to the above variable. Would you mind giving me an example of what you are talking about in this step
Matt Thubron
-Jamie
Vasen_c
I found that by selecting "Use Connection String" in the data source specification, clicking the Build button and selecting your DSN, that it would copy the connection string to your connection manager. Once you have this you should be able to set up an expression on the connection manager to create a dynamic connection string.
KABay
Ah OK. My confusion arose because you called it a "source component" and the thing you were talking about is a connection manager.
I'll take a look later - bit busy at the mo!
-Jamie
FYI: A source component is something that puts data into the pipeline in a data-flow task. Source components invariably use conenction managers (the XML Source component and Raw Source Component are exceptions to this rule)
Marcelh
In SSIS you can right click in the connection area at the bottom of the screen and select “New ADO.Net connection”. In the “Configure ADO.Net Connection Manager” select new. In the “Connection Manager” form drop down the Provider drop down box at the top of the form.
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
You will notice 3 different connection options under .Net Providers:
SqlClient Data Provider
Oracle Client Data Provider
ODBC Data Provider
When you select the ODBC Data Provider it lets you set it to the dBase drive under ODBC setup in windows. This is the only why I know of to connect to a dbf type file. The only option is to add the SQL command and then flow the results through an OLE DB destination. It is a simple SQL command: Select * from {path where data is located}. If I could pass the variable from the for each loop for the path I could process all files for a week or a month. I have tried your logic in the SQL command for the path: “Select * from = “ @varFileName but always get an error of: Connection String Property has not been Initialized. My goal is to automate this task I have been given but I can’t figure out how to process more than one file at a time. Thanks for you input so far and I appreciate any ideas or suggestions to resolve this issue going forward.
srikanthb
I have checked the connection settings and looked in the edit options for the .net componet.
innivodave
You say:
"My sorce componet is a .net ODBC componet"
Can you clarify this There is no ".net source component" in my toolbox so I'm not quite sure what you are referring to.
-Jamie
Dennis Persson
Regarding the connection string (since I think you will also need to update that with the name of each file), if you select the Connection Manager and look in the properties pane you will see a collection called Expressions. Here you can define expressions that will be evaluated and assigned to the Connection Manager's properties at run-time. I don't know about the syntax of your connection string, but it should probably be something like "Driver=blah;File Name=" + @[User::FileName]
Hope that helps.
cpf
Krzysztof Radzimski