January 27, 2009 at 9:29 am
Hi All,
I'm having difficulties recreating a task that I've done in the past in DTS
Using http://www.sqlservercentral.com/articles/Integration+Services/61621/
as a base. I have table list of servers which use to gather audit data from my various datamarts.
How do I for each server dynamically create a csv file which will have an appname as part of the file name (appname can be selected from my table list of servers) along with a datetime and have this for my filename?
I'm getting lost on using variables and incorporating them in select statements and using them as part of the filename. I get
Under Connection Manager expression for my csv i have "D:\\DataMart_Reports\\Today\\" + @[User::APP_Name] + ".CSV"
Think I'm having problem populating @[User::APP_Name]
in my foreach loop container i created a sql task
SELECT APPName
FROM dbo.SSIS_ServerList
WHERE (Server = @[USER::SQL_RS])
I use @[USER::SQL_RS] for my ado connection to each server, the same table that populated this has my APPName
debug returns
[Execute SQL Task] Error: Executing the query "SELECT APPName
FROM dbo.SSIS_ServerLis..." failed with the following error: "Must declare the scalar variable "@".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
John Zacharkan
January 27, 2009 at 10:25 am
Brian Knight has a very good(short) video on this at http://www.jumpstarttv.com/looping-through-and-loading-files-with-ssis_34.aspx
[font="Comic Sans MS"]Ira Warren Whiteside[/font]
January 27, 2009 at 10:48 am
Have you tried using expressions for the filename?
January 27, 2009 at 11:32 am
Thanks Ira - I'll take a look see.
John Zacharkan
January 27, 2009 at 11:34 am
vishal.gamji (1/27/2009)
Have you tried using expressions for the filename?
Yessir
Under Connection Manager expression for my csv i have "D:\\DataMart_Reports\\Today\\" + @[User::APP_Name] + ".CSV"
my issue is populating the variable from a select statement that also needed a parameter. see earlier post.
John Zacharkan
January 27, 2009 at 11:51 am
If u have not already done this...Can u isolate the problem by disabling the task inside the foreach task and maybe add a script task to display the AppName that u get from the query (use a message box).
This way atleast you know that your AppName is coming in correctly in the ForEach task.
Ex for msgbox in vbscript:
Step 1: Add the User::AppName in the ReadOnly properties of the script task
Step 1: Add this in the script code MsgBox(Dts.Variables("AppName").Value)
January 27, 2009 at 12:15 pm
I think i know why u r getting that error. In the ForEachLoop task, in the Variable Mappings... r u mapping the variable 0 to AppName? I have a demo project created and can email it to u. If u can, give me an email id and will send it to u.
January 27, 2009 at 12:26 pm
January 27, 2009 at 12:30 pm
Nevermind the email. See attachment.
January 27, 2009 at 2:52 pm
Still having issues my environment is Visual Studio 2008 version 9.0.30729.1 SP
[Execute SQL Task] Error: Executing the query "SELECT APPName
FROM dbo.SSIS_ServerLi..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Here's my code
John Zacharkan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply