June 8, 2010 at 10:01 am
Hi All,
I have a particular problem while creating a SSIS package. I will try to explain what’s happening with screen shots.
What I need to do is to create package that runs a SQL query to get the siteids and chuck them in to a variable. And then run a loop trough the variable and run a parameterized stored procedure and store the results in a flat file.
For that I have created ParamDay, ParamMonth, Paramyear parameters (integers) which will be used for running the stored procedure, rsSites ( an object variable to store the execute SQL task results) and ParamSiteID ( another integer data type variable to be used looping through rsSites result set)
PS: I am sorry about the quality of the screen shots ( ALT + print screen doesn’t seem to work correctly on RDP)
Project Overview – so far
http://img694.imageshack.us/f/29995385.jpg/
Execute SQL task :
http://img85.imageshack.us/f/12774875.jpg/
As you can see I changed the Result Set to Full Result Set. On the Result Set page
http://img29.imageshack.us/f/86587232.jpg/
I have changed ResultName = 0 ( string value causes an error) and selected the variable name to rsSites. So what it means is to run the SQL and store the resultset in to a object typed variable called rsSites.
For each Loop Container
http://img96.imageshack.us/i/53157910.jpg/
I have selected Foreach ADO enumerator and selected ADO object source variable to be my object variable rsSites.
On the variable Mapping page I have selected other Site variable ( ParamSiteID)
http://img442.imageshack.us/f/61796696.jpg/
So that while looping each siteid from rsSites is passed this variable to be used while running the stored procedure.
Data Flow Task ( where the problem is)
On oledb data source
http://img338.imageshack.us/i/17391229.jpg/
I selected my oledb connection and selected SQL command to be my data access mode and then I put below
exec [dbo].[dll_getMapleCashFileData] @regionOrSite = ?,@myday = ? , @mymonth = ? , @myyear = ?
I know for a fact that SSIS case sensitive so parameter names are exactly same as in procedure definition. As you can see there are 4 parameters
http://img243.imageshack.us/i/35570740.jpg/
In the parameter window I matched procedure parameters to ssis variables . After all of that when I attempt to preview so that I can map the return dataset to flat file destionation dataset which I am using a extisting file for, I get the following error
http://img411.imageshack.us/i/23756193.jpg/
Without that available columns are empty in dataflow task and can’t map the flat file destination so all package fails. If change the ? to values it works fine but it is useless if I can’t pass values from variables.
So far I tried
• To set the delay validation option to True at package level.
• To change the combination of running the stored procedure. ( put ? only didn’t make any difference)
• To set values instead of ? first do all the mappings and then change it to ? again and save the package. But as soon as I click on OK button on the window if loses all the columns.
Has anyone had the same issue? How are supposed fix this? Any idea?
Enis
June 8, 2010 at 10:13 am
hi,
Inside the SP are you using any temp table ?
Thanks & Regards,
MC
June 8, 2010 at 10:24 am
Enis
Is there any reason you can't set up a file connection and just use one data flow to move the data from the table to the file? No loops - simple.
I am sorry about the quality of the screen shots ( ALT + print screen doesn’t seem to work correctly on RDP)
I think if you put your RDP window in full screen mode then ALT + Print Screen will give you just the active window on the remote computer.
John
June 8, 2010 at 11:05 am
Hi Mc,
Yes there is.
Enis
June 8, 2010 at 11:06 am
Ok, I am about 99% sure this is your issue. SSIS has a problem when sprocs are used as the query source, I have not seem any really good workarounds. HOWEVER, I am including a package that DOES work around this issue, it isn't near as elegant as I would like but it does work. Save the attachment and take the .txt off the end, I added that because .dtsx is not an allowed type.
The first item just creates a sproc for it to use, that sproc queries sysobjects in master, please look at the query so you know what it is doing. the Get Recordset is equivalent to what you are doing. So is the setup of the F-E-L. The addition of the Set DFCommand script object modifies the query executed on each run and handles each of the variables. I don't much like this method but because of the settings in the DF task it is necessary to pass in a fully built command, this command changes with each item of the recordset, please look at the code, it is VERY straight-forward. The last item is kind of a trick, we set the DFCommand variable to be a select statement that includes all of the fields with the correct types that the sproc will return, in this example:
SELECT Field1 = CONVERT( int, 0 ), Field2 = CONVERT( varchar(30), '' ), Field3 = CONVERT( datetime, GETDATE() )What this does is make sure that the data-flow has the fields you need to build it AND that they are of the correct type, SSIS WILL BREAK if the types change at run-time. We use the DFCommand variable for the command by setting Data Access Mode in the OLE DB Source to SQL Command From Variable and then setting DFCommand as the variable to use.
I say it is a trick because at design time I am tricking SSIS into believing that the query that I entered into DFCommand will actually be used when in reality the script task completely rewrites it BUT returns results with the EXACT same field names and date types.
Is this all clear?
CEWII
June 8, 2010 at 11:09 am
Hi John,
I would have however the stored procedure only returns the information for one site at a time. I could always change it of course but this issue has become something started bothering me. I really don't understand why looping thorugh recordset would not work.
Thanks for the tip by the way. I try.
Regards
Enis
June 8, 2010 at 11:15 am
Hi Cewii,
Thanks very much for taking time to reply. I think I have followed what you were saying. I am leaving now but I will try this tomorrow and update this post.
Regards
Enis
June 8, 2010 at 11:29 am
Hi Enis,
So your SP is using temp table, as other friend mentioned even I believe this is the reason. You can follow above solution. I have attached one doc which I have noted about the 3 methods we can go ahead when the sp is using temp table. Among this I think the 3rd method is best.
Thanks & Regards,
MC
June 9, 2010 at 2:36 am
Guys,
Thank you for the useful tips.
CEWII - Is there any way to send me the project file as I can only see the executeable. I want to see how it is done.
Thanks again
Enis
June 9, 2010 at 2:45 am
enis.ertem (6/9/2010)
Guys,Thank you for the useful tips.
CEWII - Is there any way to send me the project file as I can only see the executeable. I want to see how it is done.
Thanks again
Enis
A DTSX file is not an executable, it's a package (once you've removed the .txt). Just import it into your project.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2010 at 5:04 am
Hi Guys,
Phil - it worked like a charm - thank you.
I am currently adopt mc's approach with yours to MC's 3. approach. It is very similar to your approach but you don't have to pass exec command for each iteration. It is all handled within the stored procedure.
Thank you again all for your help.
Enis
June 9, 2010 at 7:02 am
Guys,
Just to let you know there isn't significant difference between two approaches.
Appreciate your help again.
Thanks
Enis
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
