SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing a csv file into a database - inserts quotes automatically into every field


Importing a csv file into a database - inserts quotes automatically into every field

Author
Message
mrichardson 57577
mrichardson 57577
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 83
thanks for the help with the SSIS package.

I'm a newbie at SSIS and not sure what to enter in the variable mappings box?
Name...
Namespace...
Value Type...
Value...

also,
i'm not sure if its best to edit your solution or try and replicate what you have done on my own solution.
I have tried to drag a control flow on to the stage - is it best to drag it on to the control flow or data flow area?
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
I would strongly recomment reading through the stairways section on SSIS.

But every task is control flow.

Only task that is data flow enabled is a DFT.

I would strongly recommend trying to re-create it that way you can understand what is happening and you have a better chance at supporting the package rather than saying it works but I dont know how.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


mrichardson 57577
mrichardson 57577
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 83
I've had a look at the stairways section on SSIS, and re-built the package from scratch.

I can now import some of the postcode data into my SQL table successfully without the " speech marks!

However, the loop imports the same single file about 60 times and doesn't import any of the other files.
It imports the file I specify in the Flat File Connection Manager (e.g. ab.csv).

In my loop Collection properties I have specified my folder where there are about 120 csv files and in the Files box I have entered *.csv

I have created a variable name with index of 0 (but not using this variable name anywhere else in any expression).
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
Have you set the connection manager of the flat file source to use an expression on the connection string of the variable?

That will ensure that the filename on the connection manager changes with each file it finds.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


mrichardson 57577
mrichardson 57577
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 83
In Connection Manager >> Properties >> Expressions >>
I click on the ellipses and then on the next screen on the Property column drop the list down I have chosen ConnectionString.

I can see my variable and select it, but when I click on OK I get this error message:

Expression cannot be evaluated
There was no expression to compute. An Attempt was made to compute or get the string of an empty expression.

anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
can you zip up and attach your solution.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


mrichardson 57577
mrichardson 57577
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 83
anthony.green (1/22/2013)
can you zip up and attach your solution.
Ok, all seems good apart from the new import connection manager isn't setup for a dynamic connection string.

1st off give the variable value the path to 1 of the files to import (this is just so it can get the metadata)

Then...

Single click the connection manager new import
Go to the properties window
Expressions -> elipsis
Select ConnectionString -> Expression elipsis
Expand variables
Drag down @[User::csvLoop] to the expression box

Then ok all the way out


hurray!
that works thanks a lot.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
Not a problem, glad I could assist again.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search