January 27, 2010 at 9:46 pm
hi all
I am new to this SSIS package and dont have much clue in this area. Can some one please let help me in doing this -
I have a table with some data and would like to export them to an excel file (i.e. each row to an excel file or flat file) via SSIS as I believe this is more easier.
Data
PidPname
1Bike
2Car
3Wagon
4Scooter
5Bicycle
PID - is Int datatype and Pname is varchar(50)
I would like to have 5 excel files or 5 flat files at the end with each one having one row of data.
I have managed to create both excel and flat file which contains all the 5 rows
I have used a ForLoop Container to try to do this but am not getting through. Can some one please help
Thanks
Vani
January 27, 2010 at 11:46 pm
Hi Vani,
Firstly , take a dataflow task. Add a oledb source with sql command as data access mode. in the query , give a select command to get a list of a unique column from ur table . here in your case pid.
so ur quey will be "select pid from table_name".
Next, drag and drop a recordset destination. Create two variables .
Variable 1 :
Name : objRecordset
scope : Package
Datatype: object
value: system.object
Variable 2:
Name: objRecords
scope: Package
Datatype:int
value: 0
Now in the recordset destination, variablename column give objRecordset.
select pid in teh inputcolumns tab.
Come out of the dataflow task, and select a foreach container.
Go to collection tab and make the following changes :
Enumerator : Foreach ADO Enumerator
ADO Object source variable : user::objRecordset
In the Enumeration mode select Rows in the first table .
Go to variable mappings tab, select the variable user::objRecords in the variable column and give index as 0.
Place another dataflow task inside the foreach container.
select a oledb source and a flatflie desstination or excel destination.
in the source, give a select query to get all the values for a particular objRecords.
In your case it is, "select * from table_name where pid=?"
Now in the parameters tab, give parameter name as pid and in the variables select user::objRecords.
Connect this to the flatfile destination. Give an expression to give a distinct name to ur file.(in the connection manager properties, go to expressions tab and give the desired expression).
Now you should be able to create a different files for each row. Use a dataviewer in the dataflowtask inside the foreach container to check if the putput is coming row by row .
Try this out and let us know if it works fine....:-)
With Regards,
Anu..;-):hehe:
January 31, 2010 at 4:30 pm
Hi Anu
That works but it saves all data into the same excel file. I tried creating expressions in the connection manager properties but i only get error messages. I am not sure how to create one... Can you please give me an example.
I used the name as property and the expression as username but it is not working... I would like to have the Product name as the file name if thats possible.
Please help as am stuck
Thanks
Vani
January 31, 2010 at 10:23 pm
Hi Vani,
I think there is some problem with your expression , probably some type casting error.
Try the expression given below. This is working fine for me...
@[User::FilePath] + "test" +"_"+ (DT_WSTR, 1) @[User::Records] +".txt"
where @[User::FilePath] is a user variable created to hold the path where you want to store the files.
Eg : name: @[User::FilePath]
scope: Package
Datatype: string
value:C:
and @[User::Records] holds the pid value.
With Regards,
Anu..;-):hehe:
February 1, 2010 at 4:00 am
Have a look here:
http://consultingblogs.emc.com/jamiethomson/archive/2005/12/04/2458.aspx
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
February 1, 2010 at 2:35 pm
Hi Anu
Can you please send me the package that you have. I will save it on to my local machine and try to see where i am going wrong. as am certainly somewhere..
my email is - vani_r14@yahoo.co.nz
Thanks
Vani
February 2, 2010 at 2:14 am
hi john
The link is not working. I keep getting blank page both at work and at home...
thanks
vani
February 2, 2010 at 2:24 am
I believe you are commiting some mistake in the expression. place the expression i gave in the connectionstring property of the flat file connection manager.
Can u give a brief description of what you are doing in your package. Like the one i gave in my first post..
With Regards,
Anu..;-):hehe:
February 2, 2010 at 3:49 pm
Hi Anu - Here is what I have done.. I followed your instructions step by step
Created two variables (ObjRecordset of type Object at package level)
(ObjRecords of type int at package level)
Then
Added an oledb source with the sql statement – (table name products) so I entered it as –
select pid from products
Then
Added a recordset destination and set the variable name to ObjRecordset
Added – select pid in the InputColumns Tab
Added a foreach container – changed enumerator to ForEach ADO Enumerator
And set the ADO source variable to – user::ObjRecordset. In the Enumeration mode selected the radio button – select rows in the first table
In the Variable Mappings tab selected the variable User::ObjRecords and gave index as 0
Then placed another dataflow task and added an flat file destination
Selected the oledb source and gave the following query –
select * from products where pid =?
Clicked on the Parameters button and created a parameter pid and set the variables to user::ObjRecords
Connected the source to the flat file destination
In the connection manager properties of the flat file – gave the expression as below. I created the variable file_path as string & set the value as
‘C:\Documents and Settings\vraghunathan\Desktop\Results\Results-last.txt’
This is the same path that I set the connection manager of the flat file..
However I did notice that once I set the expression the name of connection manager is changing.
This results in one row of data – i.e. the last row
And am stuck here.
I am sure something is wrong with the expression and how i am setting it. When i open the expressions tab of the flat file connection manager am not sure which option to choose from the drop down list and not sure from there on...
Thanks for your help and sorry to be a pain and so dumb about this
I have attached screenshots as well to possibly help
February 2, 2010 at 9:50 pm
Hi Vani,
In the file_path variable u r assigining the file a name. and u r not using any other expression to differentiate the files that r created. so evertime the flat file manager encounters the connection manager it directly goes to the file and appends the data in the existing file or overwrites the data in the file. (depends upon if u have selected to overwrite the data in the file or not).
To solve this issue...
in the file_path variable give a path and not the filename.
file_path - c:\.....\ ..your path except the filename.txt
then in the preoperties of flat file manager ,
go to expressions... select connection manager... and in the expressions tab
expand the variables tree on the left hand side ...drag and drop the file_path variable and append the following expression "filename_" + (DT_WSTR, 1)@objRecords + ".txt"
your complete expression shud look like...
@file_path + "filename_"+ (DT_WSTR, 1)@objRecords + ".txt"
Now try this out and let me know..
With Regards,
Anu..;-):hehe:
February 3, 2010 at 10:09 pm
Hi Anu
Still not working... Am getting an error message again... please find attached
and if i remove the expression from the connection manager it only stores one row of data (the last row) and nothing else.. I still have it in the loop and nothing has been changed
not sure what to do
Vani
February 4, 2010 at 1:05 am
Got it.....
Remember SSIS variables are case-sensitive.....very very case-sensitive...
Inthe variables you defined the variable as ObjRecords and in the expression you are using it as objRecordrs.
Sure this should throw an error...
Change the case of the variable and check it..
Also dont forget to change the file_path value to the value i mentioned above...
With Regards,
Anu..;-):hehe:
February 4, 2010 at 6:38 pm
Hi Anu
I am about to give up on this and forget it... but I want to give it one last try... I am creating the whole new thing again... I will try both Excel and Flat file output..
Can you please tell me what property can I use for Excel to set the connection. I tried the one you showed me yesterday for text and also tried on excel but changed the extension to "xls" as below. I used property connection string in the excel connection manager expressions to set the below.
@[User::file_path] +"filename_"+ (DT_WSTR, 1) @[User::ProductId] + ".xls"
file_path - was set to C:\Documents and Settings\vraghunathan\Desktop\Results\.xls
and I get the error for Excel as connection string format not valid. I have attached screenshots for your reference...
BTW - I created a new variable called ProductId to hold the product when attempting to recreate the package.
Can you please help
Thanks and not sure what to do..
Vani
February 4, 2010 at 7:09 pm
Hi Anu
Further to my above post, I made one step progress.. I have now been able to create an excel file and set the expression as before
@[User::file_path] +"filename_"+ (DT_WSTR, 1) @[User::ProductId] + ".xls" by choosing the FilePath property however it does not make much sense to me to use file path instead of connection string.
However at the end of row 1 it stops and the Excel Destination becomes Red. and the output file has the column heading but no data. not sure why...
please help
vani
February 4, 2010 at 8:59 pm
Hi Anu
Please ignore my previous two posts. I worked out how to do this now. its working in excel and flat file... not sure what i changed from before as everything was same but it is working... 🙂
Thank you for all your help and patience...
Hope you have a great weekend 🙂
BTW - Nice meeting you via the forum 🙂 🙂 thanks for your effors.... I am not sure how to say thank you. If you were in Aus I would have definitely met with you for coffee or something..
Cheers and Thanks again a lot
Vani
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply