SSIS or OPENROWSET/BULK INSERT

%3CLINGO-SUB%20id%3D%22lingo-sub-1485263%22%20slang%3D%22en-US%22%3ESSIS%20or%20OPENROWSET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485263%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20very%20simple%20csv%20file%20to%20import%20into%20SQL%20Server.%20Below%20are%20some%20properties%3A%3C%2FP%3E%3CP%3E1)%20CSV%20file%20is%20of%20around%201%20GB%3C%2FP%3E%3CP%3E2)%20This%20need%20to%20be%20imported%20into%20a%20table%20and%20replaced%20on%20daily%20basis%20through%20a%20day-end%20job%20process%3C%2FP%3E%3CP%3E3)%20We%20need%20to%20migrate%20this%20on%20Azure%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMy%20Questions%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E1)%20If%20we%20use%20SSIS%20we%20have%20to%20migrate%20it%20to%20Azure%20Data%20factory%20when%20moved%20to%20cloud.%20This%20will%20be%20expensive%20when%20compared%20to%20using%20OPENROWSET%20command%20directly%20from%20SQL%20Server%20batch%20job%3F%3C%2FP%3E%3CP%3E2)%20Should%20we%20use%20OPENROWSET%20in%20this%20scenario%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDanish%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1485263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1487352%22%20slang%3D%22en-US%22%3ERe%3A%20SSIS%20or%20OPENROWSET%2FBULK%20INSERT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F708609%22%20target%3D%22_blank%22%3E%40danishwahab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20a%20similar%20scenario%20and%20used%20powershell%20bcp%20bulk%20insert%20from%20the%20computer%20that%20stored%20the%20csv%20file%20locally.%20When%20it%20became%20a%20nightly%20job%2C%20I%20added%20a%20Power%20Automate%20to%20move%20the%20file%20from%20the%20sales%20team%20sharepoint%20folder%20to%20a%20onedrive%20folder%20synced%20to%20a%20folder%20on%20the%20local%20drive%20on%20the%20computer%20that%20executed%20the%20powershell%20bcp.%20Not%20an%20elegant%20solution%2C%20but%20it%20only%20took%20a%20few%20minutes%20to%20setup%20up%20and%20I%20get%20an%20email%20with%20the%20job%20results%20every%20morning%20(different%20failures%20or%20successful%20import).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20is%20probably%20not%20the%20best%20solution%2C%20but%20maybe%20some%20part%20of%20the%20solution%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Dear All,

 

I have a very simple csv file to import into SQL Server. Below are some properties:

1) CSV file is of around 1 GB

2) This need to be imported into a table and replaced on daily basis through a day-end job process

3) We need to migrate this on Azure

 

My Questions:

1) If we use SSIS we have to migrate it to Azure Data factory when moved to cloud. This will be expensive when compared to using OPENROWSET or BULK INSERT command directly from SQL Server batch job?

2) Should we use OPENROWSET in this scenario?

 

Thanks,

Danish

1 Reply

@danishwahab 

I had a similar scenario and used powershell bcp bulk insert from the computer that stored the csv file locally. When it became a nightly job, I added a Power Automate to move the file from the sales team sharepoint folder to a onedrive folder synced to a folder on the local drive on the computer that executed the powershell bcp. Not an elegant solution, but it only took a few minutes to setup up and I get an email with the job results every morning (different failures or successful import). 

 

My solution is probably not the best solution, but maybe some part of the solution helps.