October 2, 2009 at 9:27 am
My Boss is Old school and thinks SSIS is unintuitive junk, I want to stop doing everything using ACCESS 2000!
I am winning slowly his confidence that I can get SSIS to do our ETL processes. It is amazing what you can do in Access 2000 cause u can call an SQL sp to do anything.
I get the feeling when he I get stuck like I am here that he is about to say just use access, that SSIS is worthless.
Yet he still lets me find on how to get it to do what we want. My goal is to learn how to do these projects in SSIS
so he will have the confidence let me use instead of Access. He wants this running by the end of the day for reporting. I can manaully change the dates the 7 data flow sources for today if need be, but that is not what we want to do every month obviously. Just to understand that in my questions below I do not get into 7 sps as OLE data sources because they all have the same format. sp_name 'DS0509',DateValue1,DateValue2
Here is what I need help on.
1. Where to put the 2 user::ReportingDatebegin and user::ReportingDateEnd variables. Sorry but not intuitive that people keep saying use the Control Flows SQL Task.
Why? It needs the values of these 2 variables passed to
the 'SQL command with variable' Data Flow Task OLE DB source.(which is a stored proc that needs the date to not be hard coded)
The Sp runs like this: sp_Common_Property_Declaration 'DS010509', '7/1/09', 9/30/09' these last 2 date parameters need to NOT be hard coded, they need to be the values passed to the variables above.
2.what the syntax would be when running it from another stored proc. I got this current code snippet from my boss to change for this package which is not a 2000 DTS, but an SSIS DTSX package. What would the syntax be, only talking about the variables in the code. I mean if they are named user::variable in your package what are they called when passed from T-SQL? confusion example: sp_WishCouldRunanSSIS_PackageandPassValuesToit User::ReportDateBegin=@dtReportDateBegin, User::ReportDateEnd=@dtReportDateEnd
SET @strcmdShell_String = 'DTSRun -S DB2 -U sa -P *********** -N dts_E_xls_Profiling_ExternalAll -A dtReportingBeginDate=' + Convert(varchar(10), @dtReportingBeginDate, 101) + ' -A dtReportingEndDate=' + Convert(varchar(10), @dtReportingEndDate, 101)
2. All the examples on the internet give me an XML cannot parse package error when I run the 2005 way with . Like the XPcmdShell_Master thing. Sorry to be vague by not cutting and pasting the correct syntax, I just want to know if anyone has tried to run an SSIS package from an SSMS query window and got the can't parse xml error
. If so you know just what I am talking about, can you please tell me what you did you resolve it?
October 2, 2009 at 1:54 pm
where to start... 🙂
1. Where to put the 2 user::ReportingDatebegin and user::ReportingDateEnd variables. Sorry but not intuitive that people keep saying use the Control Flows SQL Task.
Why? It needs the values of these 2 variables passed to
the 'SQL command with variable' Data Flow Task OLE DB source.(which is a stored proc that needs the date to not be hard coded)
Ok, 'people' are right in this case, you definitely need to reference these variables in the (Control Flow) Execute SQL Task. In the task properties page there is a tab for variable mapping, this is where you include these vars. So, You have a User variable of datatype integer already set up at Package level scope, in the SQL task, for the SQL source, let's say it's a static call to a stored proc, the call will be like (without the quotes) "EXEC pr_steve_is_how_old @age" You then go to the parameter mapping and click Add, which adds the parameter to the task. Select the SSIS package variable for the first field (something like user::steve_age), set the data type (int), the name (@age or just age) and the length (because it's an int, leave this as -1).
Differences to the above - the above was based on having/using an ADO.Net SQL data source, so we can use named parameters. If you're using a datasource that doesn't support named parms, you need to refer to them as 1, 2 etc AND MAKE SURE that the order (of the 1, 2, 3 etc) is the same order as how they're used/passed to the stored proc in the statement.
2.what the syntax would be when running it from another stored proc. I got this current code snippet from my boss to change for this package which is not a 2000 DTS, but an SSIS DTSX package. What would the syntax be, only talking about the variables in the code. I mean if they are named user::variable in your package what are they called when passed from T-SQL? confusion example: sp_WishCouldRunanSSIS_PackageandPassValuesToit User::ReportDateBegin=@dtReportDateBegin, User::ReportDateEnd=@dtReportDateEnd
This link talks about how to call the package execution via xp_cmdshell.
To save you looking too closely at that page, the answer to the above is you're almost there. You need to make use of the /SET commandline option. Plus you need to get the path to your variables (stepping throughthe config wizard *but not finishing it* can help you here. Notes on how to do that are on that page also.)
Here is an example, taken from the content of that page.
EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" /SET package\DataFlowTask.Variables[User::StartDate].Value;"2009-09-09" '
SO the pieces here are:
- /f to indicate to load the pkg from the file system (versus sq[L] or Dts , for the sql or ssis store respectively)
- /set to set a package variable, in this case named 'StartDate'
Hope this helps you out.
Steve.
October 2, 2009 at 2:29 pm
If you're in access - you should dig into a little VBA, and attach a reference to the DTS packages (the newer version automates SSIS). That was you can simply associate the values with parameters, etc....
It's a little prettier to deal with than the command line (which gets ugly).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply