Input line too long


I am encountering an error while trying to use a stored procedure to execute an SSIS package using the 32bit version of dtexec within a 64 bit environment. I run the following command using xp_cmdshell

SELECT @cmd = '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe"" /F <path to dtsx file> /SET \Package.Variables[User::FileName].Properties[Value];<unc path> /SET \Package.Variables[User::CreateSQL].Properties[Value];"<sql create table statement>"'

exec master..xp_cmdshell @cmd
(Code is also attached)

And it results in the error "The input line is too long." I have also tried using two qoutes ("") instead of one with the same result.

Whenever I remove spaces from "sql create table statment" the execution succeeds, but the package fails because of malformed SQL. I have also tried not using quotes around the sql create statement but that fails with "Option "table" is not valid." The statements length is only around 317 characters so this is a bit puzzling. So far it seems to have something to do with the double quotes. But I believe they are needed to avoid issues with spaces in the sql.

I could try replacing spaces with underscores and then removing them in the package, but this same command works fine within 32 bit sql 2005 housed on a 32 bit windows server 2003 machine. So i would like to get it working.

Does any know exactly what the issue is?

SELECT @cmd = '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe"" /F <path to dtsx file> /SET \Package.Variables[User::FileName].Properties[Value];<unc path> /SET \Package.Variables[User::CreateSQL].Properties[Value];"<sql create table statement>"'

exec master..xp_cmdshell @cmd

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

how have you defines @c,d

and please show us the create table data...

but why can't you just create the table outside of the package?
bnrtechAuthor Commented:
DECLARE @cmd varchar(500)

Create table tblTest (`id` Long, `text` LongText)

I forgot to mention that the create statement is used by the SSIS package to create columns within a newly made excel file. Thus, creating the table outside the package is not an option because of 64 bit OLEDB driver issues.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.