| Author |
Topic |
|
sgandhi
Posting Yak Master
115 Posts |
 Posted - 2009-02-20 : 11:59:31
|
| HI i have the following scenario.I have created a RJT table with rows in it.EGID, COUNTRY, AMOUNT, REASON, FILENAME1 CN 123 Incorrect B2R_CN_20080203.xml 2 SG 123 Incorrect B2R_SG_20080203.xml 3 CN 123 Incorrect B2R_CN_20080203.xml 4 AU 123 Incorrect B2R_AU_20080205.xml 5 AU 123 Incorrect B2R_AU_20080205.xml I want to create a xls file(s) from the data that is stored in this sql server table based on the date portion of the filename with the date in the filename concatented to the output file.EG20080203_Rejects.xls would contain the following data1 CN 123 Incorrect B2R_CN_20080203.xml 2 SG 123 Incorrect B2R_SG_20080203.xml 3 CN 123 Incorrect B2R_CN_20080203.xml 20080305_Rejects.xls would contain the following data4 AU 123 Incorrect B2R_AU_20080205.xml 5 AU 123 Incorrect B2R_AU_20080205.xml As you can see above, i need to split the data and also put the date from the filename into the name of the xls file. Can someone please help me with this |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-20 : 13:54:34
|
| How do i put the derived value into variable?and when i follow the steps in the example i get the following error. I am following these steps first to at least get this part to workNonfatal errors occurred while saving the package:Error at Package2 [Connection manager "DestinationConnectionExcel"]: 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.Error at Package2: The result of the expression "(DT_STR,4,1252) DatePart("yyyy",getdate()) +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + @[User::PATH] +".xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-21 : 02:35:58
|
| have you created variable path? have you set its value some where?the derived value can be put to variable using expression property of variable and also setting evaluate as expression property to true. |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-23 : 08:03:29
|
| In the variable i have set the value to "\\<ip address>\d$\International_Catalogs\Catalogs\Catalog Output Files\TEST.I have also set the evaluateasexpression to TRUE on that variable.On the destinationconnectionexcel, i have set the expression on the connectionstring to @[User::path] +(DT_STR,4,1252) DatePart("yyyy",getdate()) +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".xls"where am i going wrong |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-23 : 09:09:58
|
| hi, i have passed the first step. I can assign the sys date to the filename.how do i assign the derived column to a variable. |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-23 : 12:03:52
|
| HI, i really really need some help on this.How do i derive a column and pass that to a variable? So i can use it in the filename. This is what i need to be derivedSELECT distinct left(right([Original_Filename],18),14) FROM [b2r].[dbo].[RJT_International_Catalog]Also how do i get my excel document to create different documents for each date (thats in the original filename column).I figure i need to do a foreach loop, but im stuck on how to go about it. I really need some step by step instructions. |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-24 : 10:33:16
|
| Someone please help me.Do i need a foreach loop. How do i do this.I currently have a execute SQL Task that has the derived column in it.i have that joined to my data flow. Im getting confused, need expert advice |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 12:58:04
|
quote:
Originally posted by sgandhi
Someone please help me.Do i need a foreach loop. How do i do this.I currently have a execute SQL Task that has the derived column in it.i have that joined to my data flow. Im getting confused, need expert advice
you need to use for each loop with ado enumerator. use the previous query to get result onto a ADO recordset by means of execute sql task. then use for each loop to loop through recordset. inside it place a excel file destination and by means of expression builder set name along with date value.see below link toohttp://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-24 : 13:29:23
|
| Thank you so much.Only thing is i keep getting this error on the destinationexcel connection. How do i fix this. To me the file doesnt seem open, so whats doing this. Can you helpTITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Destination - Query [70]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.Error at Data Flow Task [Destination - Query [70]]: Opening a rowset for "Destination - Query" failed. Check that the object exists in the database. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-24 : 15:08:52
|
| Hi, i have set the delayvalidation on the destination connection to TRUE, but it still does not work. I did not create a sheet like in the example, because, i want multiple files not sheets.Any more ideas? |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-25 : 07:43:02
|
| Im still getting the following error even though delayvalidation = TRUE.[Destination [70]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. [Destination [70]] Error: Opening a rowset for "Destination" failed. Check that the object exists in the database. [DTS.Pipeline] Error: "component "Destination" (70)" failed validation and returned validation status "VS_ISBROKEN". How do i fix this |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-25 : 09:29:31
|
| are you trying same way as shown in link? |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-25 : 11:09:32
|
| OK, i finally got it to create individual files. But my problem now is that its not populating any data into it.It says it is, and is not failing. If i put a dataviewer on it, it shows the data but the data is not in the files that are created.Can you help on this.I really appreciate the help you have given me thus far. Its been very useful |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-25 : 12:39:07
|
| have you tried opening the files manually and checking them visually instead of DataView? maybe your problem is your elsewhere.are you sure you are pointing your data viewer to the actual files? where are you dumping the files and where is the dataviewer looking at? |
|
|
|
sgandhi
Posting Yak Master
115 Posts |
Posted - 2009-02-25 : 12:58:48
|
| the data viewer is looking at the dataflow between the data conversion and excel destination. That shows the data is going there, but nothng is writing to the files. |
|
|
|
|
PUTTING PART OF column into the output FILE NAME