September 8, 2011 at 3:27 pm
Okay, fair enough. Still doable with the same setup, but a bit more difficult.
Assuming the table definitions for all the tables are the same, just with different names, what you'll want to do is, in your ForEach task, have a script task before the data flow task.
In the script task, you'll have as a ReadOnly variable the File String variable which you're getting from each iteration of the Files object.
You'll also have a ReadWrite variable, called, lets say, TableName, as a String. This variable will get set within your script task. You can define it easily enough by just parsing the File variable and finding the Country, doing something like
Dts.Variables("TableName").Value = "dbo.crm_staging_" + File.Substring(File.IndexOf("import\") + 7, 2)
Then, in your Data Flow Task, you'll have an OLE DB Destination, which you'll use Table Name or View Name Variable - Fast Load, and supply the TableName variable.
September 8, 2011 at 3:32 pm
Okay, fair enough. Still doable with the same setup, but a bit more difficult.
Assuming the table definitions for all the tables are the same, just with different names, what you'll want to do is, in your ForEach task, have a script task before the data flow task.
In the script task, you'll have as a ReadOnly variable the File String variable which you're getting from each iteration of the Files object.
You'll also have a ReadWrite variable, called, lets say, TableName, as a String. This variable will get set within your script task. You can define it easily enough by just parsing the File variable and finding the Country, doing something like
Dts.Variables("TableName").Value = "dbo.crm_staging_" + File.Substring(File.IndexOf("import\") + 7, 2)
Then, in your Data Flow Task, you'll have an OLE DB Destination, which you'll use Table Name or View Name Variable - Fast Load, and supply the TableName variable.
---this sounds good to me, makes sense..can you do these ammends on ur package that u send me earlier so that i can play around with it, and understand it better.
September 9, 2011 at 4:51 am
please see the attached template, i have got problems with it..not able to get around my head
September 9, 2011 at 6:45 am
Check your attachment, doesn't seem to have attached. I'll take a look at the package you posted and see if I can figure out why it's not working.
September 9, 2011 at 9:08 am
I somehow managed to get to the stage that it creates tables taking dynamic variable name. But i am getting the below error. I have made the tables already but donno abt this error ..could u help?
Error at import and create tables [OLE DB Destination [17]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at import and create tables [OLE DB Destination [17]]: Opening a rowset for "dbo.webitems_" failed. Check that the object exists in the database
September 9, 2011 at 9:12 am
The error is pretty self-explanatory. The table that you're trying to access doesn't exist.
If you attach your .dtsx file I can take a look and see if I can help you, but short of that there's not much I can do.
September 9, 2011 at 9:20 am
nairdeepa (9/9/2011)
I somehow managed to get to the stage that it creates tables taking dynamic variable name. But i am getting the below error. I have made the tables already but donno abt this error ..could u help?Error at import and create tables [OLE DB Destination [17]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at import and create tables [OLE DB Destination [17]]: Opening a rowset for "dbo.webitems_" failed. Check that the object exists in the database
I have not read the entire thread, so may be off the mark, but if you are trying to create tables which already exist, you will get an error.
September 9, 2011 at 9:23 am
i am uable to do that here, if u have any email address i can forward it to u
September 9, 2011 at 9:25 am
Sure, send it to kramaswamy7@yahoo.com
BTW - you can't attach .dtsx files here, but you could zip them then attach the zip file.
September 9, 2011 at 9:29 am
sorry i got that sorted, had to provide a default variable
September 9, 2011 at 9:37 am
Hey there, saw you had sent me the email, dunno if that was before or after your last post. Do you still need any help?
September 9, 2011 at 9:38 am
sorry another question ..part of same..how to truncate table before importing data into it..i would need some statement like truncate table @tablename..@tablename is a global variable
September 9, 2011 at 9:43 am
kramaswamy (9/9/2011)
Hey there, saw you had sent me the email, dunno if that was before or after your last post. Do you still need any help?
no thats fine..ignore the package i sent..just last question on truncate below
September 9, 2011 at 9:45 am
Should be able to use the Execute SQL Task. Use SQLSourceType as Variable, and build a variable that appends the table name with TRUNCATE TABLE.
September 9, 2011 at 9:49 am
where do i pass the table variable?...sqlstatementsource would be truncate table isn't?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply