June 30, 2011 at 3:15 pm
Part 1:
I have a package that stores a table name in a variable. I use the OLE DB source to connect to my SQL Server 2008 using the Data access mode: Table name or view name variable, Variable name: User::TableName. On the Data Connection I have tried the Delay Validation to both True and False with same result.
My scenario is that I have dynamically created database tables that users need to be able to access the data. The previous system we had would create an output file in a shared network folder. This way the user never has to have access to the db server. The new system just creates the new db table but no output.
My task is to get the data from the dynamically created table and write it to a file on a network share.
I created a Create Table trigger on the database that writes the new table name to a static table. Then I fire SQL Job to run the SSIS package. My package uses the Execute SQL Task to get the newly created table name and store in a variable. Then my data flow uses the OLE DB source - Table name or view name variable to get the table name. When it runs I get the above error. I have a Flat File Destination that I use the Expression builder to create the path and use the table variable as the file name.
Part 2:
If I select the table name in the OLE DB source it runs fine, but then gets an error on the Flat File Destination: The processing of file "C:\DS_OutputFile\Select * from dbo.datatable.csv" has startedThe filename, directory name, or volume label syntax is incorrect.
The Flat File connection manager shows the correct Connection String in the properties, so why is the error show the path with the select statement???
I am new at this so I am hoping that I am just overlooking something simple...
Thanks,
Bob
June 30, 2011 at 4:58 pm
Well.... as I mentioned I am new at this. I have forgotten to delete a Script task that I was using to verify some data that was adding the information to the table name and causing the problem.
I am now able to grab the dynamically created SQL table name and dump the data to a flat file.
Unfortunately, I have found out that the new system allows the users to select from multiple and custom created outputs. This means that the table may have varying columns. I am now getting a "failed validation and returned validation status vs_needsnewmetadata".
From what I have been reading there isn't any way to change the metadata on the fly... Not good. If anyone knows of a way around this, it would be most appreciated. If it was just the 10 standard outputs, I could get a count of the columns and then select the right output. However, with the custom generated outputs, I am currently at a loss of the best way to handle this.
July 15, 2011 at 6:18 am
It seems that the variable holds the entire SQL query, thats why it joins the entire query at the end of the location path. In this case, before passing the variable to assign the source path, you can use the substring function in the expression page to extract only the table name and pass it to the further task to create a directory or folder in the table name.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply