question

Slick84 avatar image
Slick84 asked

SSIS - Export to CSV - Unusual Issue

I'm having some trouble dumping data out to a CSV from SQL Server. I am using an SSIS package with a data flow task. The data flow task has a OLEDB source which is my database. I use a SQLCMD which contains a query (query includes multiple table variables, etc and fetches a result-set) then I connect it to the Flat File Destination control. When I debug this, my flat file comes out empty. The file does get created but no data in it. I have defined the flat file connection manager and used an expression to dynamically name the file with the timestamp on it. Any idea's why this could be happening? When I preview my results in the OLEDB Source control, I do get back results, but it seems like the results aren't getting through for some reason. I put a data viewer and it just skips the data viewer while in debug mode. I have mapped out all the columns properly and everything too. All the output columns from the OLEDB source are unicode (DT_WSTR). I have defined all the columns as DT_STR in the flat file connection manager. Any help would be nice and much appreciated. Thanks,
S
ssiscsvflat-file
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image Slick84 commented ·
When I do a simple SELECT TOP 1 cola...colj FROM Table.. the data shows up on the file. Could it be a constraint of not being able to use temporary objects in the SSIS oledb source control such as table variables?
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
I'm not very familar with SSIS packages (been a while since I have built one) but why use SQLCMD to obtain the results? Isn't there a export task or something to pull the specific data out of the table into your CSV file? Then also if you want to use SQLCMD just for preference why not just use SQLCMD and output the results to a CSV file, it can do that from the command line.
0 Likes 0 ·
Kevin Feasel avatar image Kevin Feasel commented ·
What kind of SSIS task are you using to run the SQLCMD bit? Is it possible that the task you're using doesn't have anything set up in the Output Columns on the "Input and Output Properties" tab?
0 Likes 0 ·
Slick84 avatar image Slick84 commented ·
Guys, my query has multiple joins etc and thats why I was using SQLCMD within the OLEDB Source SSIS Control. The solution was very simple. I will post below as answer.
0 Likes 0 ·

1 Answer

·
Slick84 avatar image
Slick84 answered
So basically you can not use temporary objects within SSIS on runtime. In some ways this is fine because an ETL process should not have multiple transforms and reloads. It is what it is. ETL. After some research, it seems table variables can be used instead of temp tables, but only with the following statement at the top which allows for the meta data to get picked up. **When faced with a similar problem as mine, please just put SET NOCOUNT ON at the very top and your data should flow through properly.**
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.