i Can't generate my transact SQL to run ETL system

Question

i Can't generate my transact SQL to run ETL system

redinetsis

SSC Enthusiast

Points: 163
More actions
March 24, 2011 at 7:25 pm

#248633

i Can't generate my transact SQL to run ETL system,
I have an error
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.2531.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Argument "CK" for option "connection" is not valid.
NULL
My Script :
"declare @cmd varchar(1000)
declare @ssispath varchar(1000)
declare @Month as int
declare @Year as int
set @Year = 2010
set @Month = 12
select @cmd = 'dtexec /DTS "\File System\Financial_ETL_System\ETL_OLTP_STG_Fact_ProjCal" /SERVER "CK-6072443" '
select @cmd = @cmd + '/CONNECTION "10.1.2.91.CK_DWHS.dev";"\"Data Source=10.1.2.91;User ID=XXX;password=XXXXX;'
select @cmd = @cmd + 'Initial Catalog=CK_DWHS;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False; '
select @cmd = @cmd + 'Application Name=SSIS-ETL_OLTP_STG_Fact_ProjCal-{99C116FD-62D5-4185-8AC1-71BBEE3C69B6}10.1.2.91.CK_DWHS.dev;\"" '
select @cmd = @cmd + '/CONNECTION "CK-6072443\SQL2008.CK_Staging.sa";"\"Data Source=CK-6072443\SQL2008;User ID=XXXX;password=XXXXX;'
select @cmd = @cmd + 'Initial Catalog=CK_Staging;Provider=SQLNCLI10.1;Auto Translate=False;'
select @cmd = @cmd + 'Application Name=SSIS-ETL_OLTP_STG_Fact_ProjCal-{EDDE303F-7070-4321-ADF2-390819B19449}CK-6072443\SQL2008.CK_Staging.sa;\"" '
select @cmd = @cmd + '/CHECKPOINTING OFF /REPORTING V / '
select @cmd = @cmd + ' /SET \Package.Variables[User::Month].Properties[Value];"' + CAST(@Month as varchar(10)) + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::Year].Properties[Value];"' + CAST(@Year as varchar(10)) + '"'
exec master..xp_cmdshell @cmd
"
Please Help Me

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Fal Hall of Fame Points: 3143 More actions · Answer 1

You have a hyphen in: /CONNECTION "CK-6072443\SQL2008.CK_Staging.sa";

My initial guess would be that it's translating as: /CONNECTION "CK", and you might solve the issue by using [square quotes] as hyphens break names in SQL Server, ie: My-Server would need to be [My-Server].

Steve.

redinetsis SSC Enthusiast Points: 163 More actions · Answer 2

like This...?

select @cmd = 'dtexec /DTS "\File System\Financial_ETL_System\ETL_OLTP_STG_Fact_ProjCal" /SERVER [10.30.62.48]'

select @cmd = @cmd + '/CONNECTION "10.1.2.91.CK_DWHS.dev";"\"Data Source=10.1.2.91;User ID=XXX;password=XXXX;'

select @cmd = @cmd + 'Initial Catalog=CK_DWHS;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False; '

select @cmd = @cmd + 'Application Name=SSIS-ETL_OLTP_STG_Fact_ProjCal-{99C116FD-62D5-4185-8AC1-71BBEE3C69B6}10.1.2.91.CK_DWHS.dev;\"" '

select @cmd = @cmd + '/CONNECTION "CK-6072443\SQL2008.CK_Staging.sa";"\"Data Source=CK-6072443\SQL2008;User ID=XXXX;password=XXXXX;'

select @cmd = @cmd + 'Initial Catalog=CK_Staging;Provider=SQLNCLI10.1;Auto Translate=False;'

select @cmd = @cmd + 'Application Name=SSIS-ETL_OLTP_STG_Fact_ProjCal-{EDDE303F-7070-4321-ADF2-390819B19449}CK-6072443\SQL2008.CK_Staging.sa;\"" '

select @cmd = @cmd + '/CHECKPOINTING OFF /REPORTING V / '

select @cmd = @cmd + ' /SET \Package.Variables[User::Month].Properties[Value];"' + CAST(@Month as varchar(10)) + '"'

select @cmd = @cmd + ' /SET \Package.Variables[User::Year].Properties[Value];"' + CAST(@Year as varchar(10)) + '"'

exec master..xp_cmdshell @cmd

but next erro

Microsoft (R) SQL Server Execute Package Utility

Version 10.0.2531.0 for 32-bit

NULL

Option "10.1.2.91.CK_DWHS.dev;Data Source=10.1.2.91;User ID=dev;password=opexdev;Initial Catalog=CK_DWHS;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False; Application Name=SSIS-ETL_OLTP_STG_Fact_ProjCal-{99C116FD-62D5-4185-8AC1-71BBEE3C69B6}10.1.2.91.CK_DWHS.dev;" is not valid.

NULL

Plese help

Thx

Fal Hall of Fame Points: 3143 More actions · Answer 3

You still have a similar problem, notably that the string is breaking in the wrong place. What the message is telling you is that ALL that string is being interpreted as a single value/parameter. If you're not, I suggest replacing the execute clause with a "select @cmd", perhaps with a text output, and reading it through rather than trying to execute immediately. This way you can see just what you're getting. One thing I notice is that you're using \ as an escape character, ie: \" to insert a literal " char. But you're also using it as a literal \ char as in specifying the "CK-6072443\SQL2008" instance. You may need \\ for those literal \ chars. But the short of it is your string is still breaking in the wrong places.

However, I think you have a bigger problem, as in: did you really post your server name, database name and SA password on the internet? You should never EVER do that. If so, change it immediately, along with the DEV user password.

Steve.